const nodeExcel = require('excel-export');
const moment = require('moment');
const util = require('util');

module.exports = class extends think.Model {
  get relation() {
    return {
      zzjginfo: {
        type: think.Model.HAS_ONE,
        model: 'zzjg',
        key: 'zzjg',
        fKey: 'id'
      },
      roleinfo: {
        type: think.Model.HAS_MANY,
        model: 'cmswing/auth_user_role',
        key: 'id',
        fKey: 'user_id'
      },
      cancel_remark: {
        type: think.Model.HAS_ONE,
        model: 'remarks',
        key: 'cancel_remark_id',
        fKey: 'id'
      }
    };
  }
  /**
     * 用户登录认证
     * @param  string  $username 用户名
     * @param  string  $password 用户密码
     * @param  integer $type     用户名类型 （1-用户名，2-邮箱，3-手机，4-UID）
     * @param  {int} login 登陆方式 0-前台登陆 ， 1-后台登陆
     * @return integer           登录成功-用户ID，登录失败-错误编号
     */
  async signin(username, password, ip, type = 1, login = 0) {
    let map = {};
    switch (type) {
      case 1:
        map.username = username;
        break;
      case 2:
        map.email = username;
        break;
      case 3:
        map.mobile = username;
        break;
      case 4:
        map.id = username;
        break;
      case 5:
        map = {
          username: username,
          mobile: username,
          _logic: 'OR'
        };
        break;
      default:
        return 0; // 参数错误
    }
    let users = await this.where(map).select();
    let user = null;

    // 如果是后台登陆
    if (users && users.length > 1 && login == 1) {
      users = users.filter(function(item) {
        return item.is_admin == 1;
      });
    }

    // 密码过滤
    if (users && users.length > 1) {
      users = users.filter(function(item) {
        return item.password == password;
      });
      if (users.length == 0) {
        return -2;
      }
    }

    // 可登陆用户是否存在多个
    if (users && users.length > 1) {
      return users;// 返回多个用户
    } else if (users && users.length > 0) {
      user = users[0];
    }

    if (!think.isEmpty(user) && user.status == 1) {
      // 验证是否是管理用户
      if (login == 1) {
        if (user.is_admin == 0) {
          return -3;// 不是管理用户，不能登陆后台
        }
      }
      /* 验证用户密码 */
      if (password === user.password) {
        await this.autoLogin(user, ip);// 更新用户登录信息，自动登陆
        /* 记录登录SESSION和COOKIES */
        const userInfo = {
          ...user,
          'uid': user.id,
          'last_login_time': new Date().valueOf(),
          'last_login_ip': _ip2int(ip)
        };

        return userInfo; // 登录成功，返回用户信息
      } else {
        return -2; // 密码错误
      }
    } else {
      return -1; // 用户不存在或被禁用
    }
  }

  /**
     * 自动登录用户
     * @param  integer $user 用户信息数组
     */
  async autoLogin(user, ip) {
    /* 更新登录信息 */
    const data = {
      'last_login_time': new Date().valueOf(),
      'last_login_ip': _ip2int(ip)
    };
    const use = await this.where({id: user.id}).update(data);
    await this.where({id: user.id}).increment('login');
  }

  /**
     * 根据用户ID获取用户昵称
     * @param  integer $uid 用户ID
     * @return string       用户昵称
     */

  async get_nickname(uid) {
    uid = uid || 0;
    // TODO 缓存处理后续
    let name;
    // 设置缓存 key 为 username，有效期为 2 个小时
    const info = await this.field('nickname').cache({timeout: 2 * 3600 * 1000}).find(uid);
    // console.log(info);
    name = info.username;
    return name;
  }

  /**
   * 根据用户ID获取用户昵称
   * @param  integer $uid 用户ID
   * @return string       用户真实姓名
   */

  async get_realname(uid) {
    uid = uid || 0;
    // TODO 缓存处理后续
    let name;
    // 设置缓存 key 为 username，有效期为 2 个小时
    const info = await this.field('real_name').cache({timeout: 2 * 3600 * 1000}).find(uid);
    // console.log(info);
    name = info.real_name;
    return name;
  }

  /**
   * 根据用户ID获取用户昵称
   * @param  integer $uid 用户ID
   * @return string       用户denglu ming
   */

  async get_username(uid) {
    uid = uid || 0;
    // TODO 缓存处理后续
    let name;
    // 设置缓存 key 为 username，有效期为 2 个小时
    const info = await this.field('username').cache({timeout: 2 * 3600 * 1000}).find(uid);
    // console.log(info);
    name = info.username;
    return name;
  }
  async export_userqx(v, status) {
    const conf = {};
    conf.name = 'mysheet';// 表格名
    let cols;
    if (status == 1) {
      cols = [{
        caption: '用户ID',
        type: 'number'
      }, {
        caption: '用户姓名',
        type: 'string'
      }, {
        caption: '所属核心企业',
        type: 'string'
      }, {
        caption: '管理组',
        type: 'string'
        // width:280
      }, {
        caption: '创建时间',
        type: 'string'
      }];
    } else {
      cols = [{
        caption: '用户ID',
        type: 'number'
      }, {
        caption: '用户姓名',
        type: 'string'
      }, {
        caption: '所属核心企业',
        type: 'string'
      }, {
        caption: '管理组',
        type: 'string'
        // width:280
      }, {
        caption: '创建时间',
        type: 'string'
      }, {
        caption: '停用时间',
        type: 'string'
      }];
    }
    const alldata = new Array();
    for (let i = 0; i < v.length; i++) {
      const role = await get_member_role(v[i].id);
      const zzjg = await get_zzjg(v[i].zzjg);
      const arr = new Array();
      arr.push(v[i].id);
      arr.push(v[i].username);
      if (think.isEmpty(zzjg)) {
        arr.push('无');
      } else {
        arr.push(zzjg);
      }
      if (think.isEmpty(role)) {
        arr.push('无');
      } else {
        arr.push(role.desc);
      }
      arr.push(dateformat('Y-m-d H:i:s', v[i].reg_time));
      if (status != 1) {
        arr.push(dateformat('Y-m-d H:i:s', v[i].locked_time));
      }
      alldata.push(arr);
    }
    conf.cols = cols;
    conf.rows = alldata;// 填充数据
    const result = nodeExcel.execute(conf);
    const data = new Buffer(result, 'binary');
    return data;
  }

  async export_user(v, modelexp) {
    const conf = {};
    conf.name = 'mysheet';// 表格名
    let cols;
    if (modelexp == 1) {
      cols = [{
        caption: '序号',
        type: 'number'
      }, {
        caption: '会员姓名',
        type: 'string'
      }, {
        caption: '会员主体',
        type: 'string'
      }, {
        caption: '电话号码',
        type: 'string'
        // width:280
      }, {
        caption: '公司/商户名称',
        type: 'string'
      }, {
        caption: '纳税人识别号',
        type: 'string'
      }, {
        caption: '身份证号码',
        type: 'string'
      }, {
        caption: '会员类型',
        type: 'string'
      }, {
        caption: '员工编号',
        type: 'string'
      }, {
        caption: '认证申请时间',
        type: 'string'
      }, {
        caption: '认证审核时间',
        type: 'string'
      }, {
        caption: '认证状态',
        type: 'string'
      }];
    } else if (modelexp == 2) {
      cols = [{
        caption: '序号',
        type: 'number'
      }, {
        caption: '会员姓名',
        type: 'string'
      }, {
        caption: '会员昵称',
        type: 'string'
      }, {
        caption: '会员主体',
        type: 'string'
      }, {
        caption: '电话号码',
        type: 'string'
        // width:280
      }, {
        caption: '公司/商户名称',
        type: 'string'
      }, {
        caption: '纳税人识别号',
        type: 'string'
      }, {
        caption: '身份证号码',
        type: 'string'
      }, {
        caption: '会员类型',
        type: 'string'
      }, {
        caption: '员工编号',
        type: 'string'
      }, {
        caption: '认证申请时间',
        type: 'string'
      }, {
        caption: '认证审核时间',
        type: 'string'
      }, {
        caption: '认证状态',
        type: 'string'
      }];
    } else if (modelexp == 3) {
      cols = [{
        caption: '序号',
        type: 'number'
      }, {
        caption: '会员姓名',
        type: 'string'
      }, {
        caption: '会员主体',
        type: 'string'
      }, {
        caption: '电话号码',
        type: 'string'
        // width:280
      }, {
        caption: '公司/商户名称',
        type: 'string'
      }, {
        caption: '纳税人识别号',
        type: 'string'
      }, {
        caption: '身份证号码',
        type: 'string'
      }, {
        caption: '会员类型',
        type: 'string'
      }, {
        caption: '员工编号',
        type: 'string'
      }, {
        caption: '注销时间',
        type: 'string'
      }];
    } else if (modelexp == 4) {
      cols = [{
        caption: '序号',
        type: 'number'
      }, {
        caption: '会员姓名',
        type: 'string'
      }, {
        caption: '会员主体',
        type: 'string'
      }, {
        caption: '电话号码',
        type: 'string'
        // width:280
      }, {
        caption: '公司/商户名称',
        type: 'string'
      }, {
        caption: '身份证号码',
        type: 'string'
      }, {
        caption: '冻结起始时间',
        type: 'string'
      }, {
        caption: '预设冻结期限',
        type: 'string'
      }, {
        caption: '解冻时间',
        type: 'string'
      }];
    } else if (modelexp == 5) {
      cols = [{
        caption: '序号',
        type: 'number'
      }, {
        caption: '会员姓名',
        type: 'string'
      }, {
        caption: '会员主体',
        type: 'string'
      }, {
        caption: '电话号码',
        type: 'string'
        // width:280
      }, {
        caption: '公司/商户名称',
        type: 'string'
      }, {
        caption: '身份证号码',
        type: 'string'
      }, {
        caption: '问题单数量',
        type: 'number'
      }, {
        caption: '权限状态',
        type: 'string'
      }];
    }

    const alldata = new Array();
    for (let i = 0; i < v.length; i++) {
      const arr = new Array();
      arr.push(i + 1);
      arr.push(v[i].real_name);
      if (modelexp == 2) {
        arr.push(v[i].nickname);
      }
      if (v[i].org_type == 0) {
        arr.push('个人');
      } else {
        arr.push('企业(个体)');
      }
      arr.push(v[i].mobile);
      arr.push(v[i].gsmc);
      if (modelexp == 4 || modelexp == 5) {

      } else {
        arr.push(v[i].nsrsbh);
      }
      arr.push(v[i].user_code);
      if (modelexp == 4) {
        arr.push(dateformat('Y-m-d H:i:s', v[i].locked_time));
        if (v[i].freeze_limit == 0) {
          arr.push('不限');
        } else if (v[i].freeze_limit == 1) {
          arr.push('一天');
        } else if (v[i].freeze_limit == 2) {
          arr.push('三天');
        } else {
          arr.push('暂无');
        }
        arr.push(dateformat('Y-m-d H:i:s', v[i].opening_time));
      } else if (modelexp == 5) {
        arr.push(v[i].procount);
        if (v[i].status == 0) {
          arr.push('冻结');
        } else if (v[i].status == 1) {
          arr.push('可用');
        } else {
          arr.push('暂无');
        }
      } else {
        if (v[i].groupid == 11) {
          arr.push('普通会员');
        } else if (v[i].groupid == 12) {
          arr.push('核心企业');
        } else {
          arr.push('暂无');
        }
        arr.push(v[i].ygbh);
        if (modelexp == 3) {
          arr.push(dateformat('Y-m-d H:i:s', v[i].cancel_remark.remark_time));
        } else {
          if (v[i].update_time == 0) {
            arr.push('空');
          } else {
            arr.push(dateformat('Y-m-d H:i:s', v[i].update_time));
          }
          if (v[i].shenheshijian == null) {
            arr.push('空');
          } else {
            arr.push(dateformat('Y-m-d H:i:s', v[i].shenheshijian));
          }
          if (v[i].zhucestatus == 2) {
            arr.push('已通过');
          } else if (v[i].zhucestatus == -1) {
            arr.push('已驳回');
          } else if (v[i].zhucestatus == 1) {
            arr.push('审核中');
          } else {
            arr.push('未提交');
          }
        }
      }
      alldata.push(arr);
    }
    conf.cols = cols;
    conf.rows = alldata;// 填充数据
    const result = nodeExcel.execute(conf);
    const data = new Buffer(result, 'binary');
    return data;
  }

  async listactionsafeOrder(query, currentPage = 1, pageSize = 20) {
    let queryStr = '';
    for (const key in query) {
      if (query.hasOwnProperty(key)) {
        const element = query[key];
        if (!queryStr) {
          queryStr = ` WHERE (\`${key}\` LIKE  '%${element}%')`;
        } else {
          queryStr += `AND (\`${key}\` LIKE  '%${element}%')`;
        }
      }
    }
    const sql = 'SELECT %s  from (\n' +
      '                        select id,\n' +
      '                               real_name,\n' +
      '                               gsmc,\n' +
      '                               user_code,\n' +
      '                               org_type,\n' +
      '                               mobile,\n' +
      '                               freeze_status,\n' +
      '                               sum(coun) procount\n' +
      '                        from (\n' +
      '                               select b.id,\n' +
      '                                      b.real_name,\n' +
      '                                      b.gsmc,\n' +
      '                                      b.user_code,\n' +
      '                                      b.org_type,\n' +
      '                                      b.mobile,\n' +
      '                                      b.freeze_status,\n' +
      '                                      count(a.problem_userid) coun\n' +
      '                               from cmswing_order a\n' +
      '                                      left join cmswing_member b on a.problem_userid = b.id where\n' +
      "                                    a.problem_type ='问题订单' group by\n" +
      '                                    a.problem_userid\n' +
      '                                 union all\n' +
      '                                 select\n' +
      '                                    b.id,\n' +
      '                                    b.real_name,\n' +
      '                                    b.gsmc,\n' +
      '                                    b.user_code,\n' +
      '                                    b.org_type,\n' +
      '                                    b.mobile,\n' +
      '                                    b.freeze_status,\n' +
      '                                    count(a.faburen) coun\n' +
      '                                 from\n' +
      '                                    cmswing_gongxufabu a\n' +
      '                                      left join cmswing_member b on a.faburen = b.id where\n' +
      "                                    a.problem_type ='问题信息' group by\n" +
      '                                    a.faburen\n' +
      '                             ) as U group by\n' +
      '                             id,\n' +
      '                             real_name,\n' +
      '                             gsmc,\n' +
      '                             user_code,\n' +
      '                             org_type,\n' +
      '                             mobile,\n' +
      '                             freeze_status\n' +
      '                      ) AS G';
    const countData = await this.query(util.format(sql, ' COUNT(*) ') + queryStr + ' LIMIT 1;');
    const data = await this.query(util.format(sql, '*') + queryStr);
    const count = countData[0]['COUNT(*)'];
    const totalPages = Math.ceil(count / pageSize);
    return {currentPage, pageSize, count, data, totalPages};
  }

  async listproblem(id) {
    const sql = `select * from
  (select
      a.id id,
       a.problem_userid problem_userid,
       a.order_no order_no,
       b.real_name real_name,
       b.gsmc gsmc,
       b.mobile pmobile,
       a.shangpingleibie shangpingleibie,
       a.diqu diqu,
       a.yewuleixing yewuleixing,
       c.username username,
       c.zzjg zzjg,
       c.mobile mobile,
       a.decision_problem_time decision_problem_time,
       a.decision_problem_node decision_problem_node,
       1 dat
from cmswing_order a
       left join cmswing_member b on a.problem_userid = b.id
       left join cmswing_member c on a.decision_problem_userid = c.id
       where
     a.problem_type ='问题订单'
  union all
  select
     a.id id,
     a.faburen problem_userid,
     a.fabu_no order_no,
     b.real_name real_name,
     b.gsmc gsmc,
     b.mobile pmobile,
     a.shangpingleibie shangpingleibie,
     a.diqu diqu,
     a.xinxileixing yewuleixing,
     c.username username,
     c.zzjg zzjg,
     c.mobile mobile,
     a.decision_problem_time decision_problem_time,
     a.decision_problem_node decision_problem_node,
     2 dat
  from
     cmswing_gongxufabu a
       left join cmswing_member b on a.faburen = b.id
       left join cmswing_member c on a.decision_problem_userid = c.id
       where
     a.problem_type ='问题信息') U
where U.problem_userid = ${id}`;
    const data = await this.query(sql);
    return data;
  }
};
